package mudbconnection;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import muobject.Physical;
import muobject.Questionnaire;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("DBQuestionnairesService")
public class DBQuestionnairesService implements DBQuestionnaires{
	private JdbcTemplate jdbct;

	@Autowired
	public DBQuestionnairesService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}
	
	public List<Questionnaire> getAllQuestionnaires(){
		try{
			@SuppressWarnings("unchecked")
			List<Questionnaire> questionnaire = (List<Questionnaire>)jdbct.query("Select * from Questionnaires ", new QuestionnaireMapper());
			return questionnaire;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public String getTestDate(String stno, String year, String month, int frequency) {
		try {
			String testDate = (String) jdbct
					.queryForObject("Select TestDate from Questionnaires " +
							"where Stno like ? and Years like ? and Month like ? and Frequency like ? " +
							"group by TestDate " +
							"order by TestDate ", 
							new Object[] { stno, year, month, frequency }, java.lang.String.class);
			return testDate;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public List getTestDateByYearMonthStno(String stno, String year, String month) {
		try {
			
			@SuppressWarnings("unchecked")
			List testDate = (List) jdbct
					.queryForList("Select TestDate from Questionnaires " +
							"where Stno like ? and Years like ? and Month like ? " +
							"group by TestDate " +
							"order by TestDate ", 
							new Object[] { stno, year, month });
			return testDate;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public List getTestDateByYearStno(String stno, String year) {
		try {
			
			@SuppressWarnings("unchecked")
			List testDate = (List) jdbct
					.queryForList("Select TestDate from Questionnaires " +
							"where Stno like ? and Years like ? " +
							"group by TestDate " +
							"order by TestDate ", 
							new Object[] { stno, year });
			return testDate;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public List getListAnswer(String stno, String year, String testDate, String qno){
		try {
			List answer = (List) jdbct.queryForList("Select Answer from Questionnaires " +
							"where Stno=? and Years=? and TestDate=? and Qno=? ", 
							new Object[] { stno, year, testDate, qno });
			return answer;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public List getListAnswerByYearTestdateStno(String stno, String year, String testDate){
		try {
			List answer = (List) jdbct.queryForList("Select Qno, Answer from Questionnaires " +
							"where Stno=? and Years=? and TestDate=? ", 
							new Object[] { stno, year, testDate });
			return answer;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public String getAnswer(String stno, String year, String month, String testDate, String qno){
		try {
			String answer = (String) jdbct
					.queryForObject("Select Answer from Questionnaires " +
							"where Stno=? and Years=? and Month=? and TestDate=? " +
							"and Qno=?", 
							new Object[] { stno, year, month, testDate, qno }, java.lang.String.class);
			return answer;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public boolean setQuestionnaire(Questionnaire questionnaire) {
		try{
			jdbct.update("insert into Questionnaires (stno, Years, Month, Frequency, TestDate, Qno, Answer, Block) " +
					"values(?,?,?,?,?,?,?,?)", 
				     new Object[] {questionnaire.getStno(), questionnaire.getYears(), questionnaire.getMonth(),
					questionnaire.getFrequency(), String.valueOf(questionnaire.getTestDate()), questionnaire.getQno(),
					questionnaire.getAnswer(), questionnaire.getBlock()});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean updateQuestionnaire(Questionnaire questionnaire){
		try{
			jdbct.update("update Questionnaires set Answer=?, TestDate=?, Block=? " +
					"where stno=? and Years=? and Month=? and Frequency=? and Qno=?", 
				     new Object[] {questionnaire.getAnswer(), String.valueOf(questionnaire.getTestDate()), questionnaire.getBlock(), 
					questionnaire.getStno(), questionnaire.getYears(), questionnaire.getMonth(), 
					questionnaire.getFrequency(), questionnaire.getQno()});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}

	public boolean deleteQuestionnaire(String stno, String year, String month, String qno, String testDate){
		try{
			jdbct.update("delete from Questionnaires where stno=? and Years=? and Month=? and Qno=? and TestDate=?", 
				     new Object[] {stno, year, month, qno, testDate});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public List<Questionnaire> getSpicalStudentQuestionnaires(String stno){
		try{
			@SuppressWarnings("unchecked")
			List<Questionnaire> questionnaires = (List<Questionnaire>)jdbct.query("Select * from Questionnaires where Stno=?", 
					new Object[]{ stno },
					new QuestionnaireMapper());
			return questionnaires;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	class QuestionnaireMapper implements RowMapper{
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException{
			Questionnaire questionnaire = null;
			try{
				questionnaire = new Questionnaire(rs.getInt("Qid"), rs.getString("Stno"), rs.getString("Years")
						, rs.getString("Month"), rs.getInt("Years")
						, rs.getDate("TestDate"),rs.getString("Qcno"), rs.getString("Answer"), rs.getString("Block")
						, rs.getDate("Createdate"), rs.getDate("Updatedate"));
			}catch(Exception e){
				System.out.println(e.getMessage());
			}
			return questionnaire;
		}
	}
}
